package com.zh.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

//使用JDBC实现课工场数据的增删改查
public class JDBCZSGC {
	Connection connection = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	//获取数据库连接
	public void getConnection(){
		// 加载不同数据库厂商提供的驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");

			// (1)铺路(获取连接Connection)
			String url = "jdbc:mysql://localhost:3306/kgcnews";
			// try catch 语法 try catch体{}内的变量 不可以在外面调用
			connection = DriverManager.getConnection(url, "root", "lee");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		

		

	}
	
	
	
	//增加新闻信息
	public void addNews( int categoryId,String title, String summary, String content,String author,Date createDate){
		try {
			//几个注意的地方:
			//1.sql语句要写写好,只有在mysql种能执行成功,他才能在JAVA里执行.可以把生成的SQL拿到数据库去执行一下.
			//2.第一个字段是ID.你数据库中设置的为自增长了,就不需要再给值了.就是我刚删掉的 setint id那个字段
			//
			
			
			
			this.getConnection();
			String sql="INSERT INTO news_detail(categoryId,title,summary,content,author,createDate) VALUES(?,?,?,?,?,?)";
			pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, categoryId);
			pstmt.setString(2, title);
			pstmt.setString(3, summary);
			pstmt.setString(4, content);
			pstmt.setString(5, author);
			pstmt.setTimestamp(6, new Timestamp(createDate.getTime()));
			
		int i =	pstmt.executeUpdate();
		if(i>0){
			System.out.println("插入新闻成功!");
		}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
	}
	
	
	//删除特定新闻方法
	
	public void deleteNews(int id){
		try {
			this.getConnection();
			String sql = "DELETE FROM news_detail WHERE id=?";
			pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, id);
			
		int i =	pstmt.executeUpdate();
		if(i>0){
			System.out.println("删除新闻成功!");
		}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
		
	}
	
	//修改特定新闻标题的方法
	public void updateNews(int id,String title){
		try {
			this.getConnection();
			String sql = "UPDATE news_detail SET title=? WHERE id=2";
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, title);
			pstmt.setInt(2,id);
			//zd le ba知道了把写2就是写死了 ?代替
			
		int i =	pstmt.executeUpdate();
		if(i>0){
			System.out.println("修改新闻成功!");
		}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
		
	}
	
	
	
	//查询全部新闻信息
	public void getNewsList(){
		try {
			this.getConnection();
			//我日啊  刚刚跟你讲的 白讲了   这个英文 讲的不是很明显嘛 unknown 不知道
			String sql = "SELECT id,categoryId,title,summary,content,author,createDate FROM news_detail ";
			pstmt = connection.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				int cid=rs.getInt("categoryId");
				String newstitle = rs.getString("title");
				String sy=rs.getString("summary");
				String co=rs.getString("content");
				String ou=rs.getString("author");
				Timestamp createdate=rs.getTimestamp("createDate");
				System.out.println(id+"\t"+newstitle+"\t"+cid+"\t"+sy+"\t"+co+"\t"+ou+"\t"+createdate);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
}
	
	
	
	
	//查询特定标题的新闻信息
	public void getNewsByTitle(String title){
			try {
				this.getConnection();
				// (2)下圣旨(sql命令)?占位符
				String sql = "SELECT id,title FROM news_detail where title like ?";
				System.out.println(sql);
				// (3)找个小太监帮皇上执行圣旨(Statement/PreparedStatement)
				//哪里不是不需要带参
			
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, title);
				//在sql语句的第一个问号的位置填充title
				// (4)拉回西瓜(返回结果集ResultSet)
				rs = pstmt.executeQuery();
				while (rs.next()) {
					int id = rs.getInt("id");
					String newstitle = rs.getString("title");
					System.out.println(id + "\t" + newstitle);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				// (5)关闭城门(释放资源)
				//作用域
				try {
					rs.close();
					pstmt.close();
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
	}
	public static void main(String[] args) {
		JDBCZSGC ND=new JDBCZSGC();
		//ND.getNewsByTitle("%a%");
		//这个3就不用给//你把new Date 改下 日期不对 嗯
		//这个地方ID 我没加 执行一次 变成4 执行一次bian'cheng
		//ND.addNews( 1, "test", "test", "test", "lee",new Date());
		//ND.updateNews(2, "newtitle");
	ND.getNewsList();
		//ND.deleteNews(3);
	}
}
